﻿using Ledict.ConditionExtract.Model;
using Ledict.Core.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ledict.ConditionExtract.DAL
{
    public class SYS_ArgumentTextRelDAL
    {
        /// <summary>
        /// 获取存储争议焦点内容与争议焦点要素表
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysArgumentTextRelData(string causeId)
        {
            string strSql = string.Format(@"  select distinct id,CaseId,ATR_Content,ATR_CheckType from
                        (
                        (select 0 as id, CaseId,ATR_Content,ATR_TypeID,ATR_CheckType  from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where CauseId='{0}' and  ATR_TypeID='')
                        union
                        (select 1 as id, CaseId,ATR_Content,ATR_TypeID,ATR_CheckType  from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where CauseId='{0}' and ATR_TypeID<>'' and ATR_CheckType is null)
                        union
                        (select 2 as id, CaseId,ATR_Content,ATR_TypeID,ATR_CheckType  from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where CauseId='{0}' and ATR_TypeID<>'' and ATR_CheckType=1)
                        )a ", causeId);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取争议焦点数量
        /// </summary>
        /// <returns></returns>
        public int GetSysArgumentCount(string causeId)
        {
            Database db = DatabaseFactory.CreateDatabase("JudgeProcess");
            string strSql = string.Format(@" select count(1)  from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where CauseId='{0}' and  ATR_TypeID=''", causeId);
            DbCommand oCmd = db.GetSqlStringCommand(strSql.ToString());
            DataSet table = db.ExecuteDataSet(oCmd);
            if (table != null && table.Tables[0] != null && table.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(table.Tables[0].Rows[0][0]);
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取争议焦点内容根据caseid与内容
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysArgumentTextRelData()
        {
            string strSql = "  select * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] ";
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取争议焦点内容根据caseid与内容
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysArgumentTextRelDataByTj(string condition)
        {
            string strSql =string.Format("  select * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where {0} ",condition);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取争议焦点分组及数量
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysArgumentTop(string ccID)
        {
            string strSql = string.Format(@"    select ATR_TypeID, count(ATR_TypeID) as atrNum from[Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_TypeID<>'' and ATR_TypeID in( select cc_id  from [Ldt_JudgeDocSystem].[dbo].[SYS_CaseCause]
	                        where ','+AllParentId+',' like'%,{0},%') group by ATR_TypeID order by atrNum desc ", ccID);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 修改争议焦点选中状态
        /// </summary>
        /// <returns></returns>
        public bool UpdateArgumentState(int state, string caseId, string atrContent)
        {
            string strSql = string.Format(@"    update[dbo].[SYS_ArgumentTextRel] set[ATR_CheckType]={0} where CaseId = '{1}'
and ATR_Content = '{2}'", state, caseId, atrContent);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 删除争议焦点内容与争议焦点要素表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DelSYS_ArgumentTextRel(string caseId, string atrContent)
        {
            string strSql = string.Format(@"    delete from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where CaseId = '{0}'
and ATR_Content = '{1}'", caseId, atrContent);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 插入存储争议焦点内容与争议焦点要素表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddSYS_ArgumentTextRel(SYS_ArgumentTextRel model)
        {

            string strSql = string.Format(" insert into [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel](ATR_ID,CaseId,ATR_Content,ATR_TypeID,CauseId,ATR_DataSource,ATR_CaseType,ATR_ParagraphCode,ATR_CourtID,ATR_BookType,AF_ID)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}') ",
                model.ATR_ID, model.CaseId, model.ATR_Content, model.ATR_TypeID, model.CauseId,model.ATR_DataSource,model.ATR_CaseType,model.ATR_ParagraphCode,model.ATR_CourtID,model.ATR_BookType,model.AF_ID);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获取争议焦点内容
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysArgumentTextRelDataNew(SYS_ArgumentTextRel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(" select CaseId,CaseCode,ATR_Content,ATR_TypeID,ATR_CheckType,CauseId,ATR_CaseType,ATR_BookType,ATR_ParagraphCode,ATR_CourtID,ATR_ID  from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where  CauseId='{0}' and ATR_CaseType='{1}' and ATR_BookType='{2}' and ATR_ParagraphCode='{3}' and ATR_DataSource='{4}' ", model.CauseId, model.ATR_CaseType, model.ATR_BookType, model.ATR_ParagraphCode, model.ATR_DataSource));
            if (!string.IsNullOrEmpty(model.ATR_CourtID))
            {
                strSql.Append(string.Format(" and ATR_CourtID='{0}' ", model.ATR_CourtID));
            }
            if (!string.IsNullOrEmpty(model.ATR_Content))
            {
                strSql.Append(string.Format(" and ( ATR_Content like'%{0}%' or CaseCode like'%{0}%' or CaseId  like'%{0}%' ) ", model.ATR_Content));
            }
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取争议焦点内容
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysArgumentTextData(string CaseCode)
        {
            string strSql = string.Format(@"  select ATR_Content,ATR_TypeID  FROM[Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_DataSource = 1 and CaseCode = '{0}' ", CaseCode);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }


        /// <summary>
        /// 获取重新分析数据
        /// </summary>
        /// <param name="dta"></param>
        /// <returns></returns>
        public DataSet dtReAnalyData(Data dta)
        {
            StringBuilder strSql = new StringBuilder();
            string str = "";
            if (!string.IsNullOrEmpty(dta.Ljd))
            {
                str += string.Format(" and ATR_ParagraphCode in({0}) ", dta.Ljd);
            }
            if (!string.IsNullOrEmpty(dta.Fy))
            {
                str += string.Format(" and ATR_CourtID in({0}) ", dta.Fy);
            }
            strSql.Append(string.Format(@"  select  top {1} * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where  ATR_DataSource=1 and ATR_TypeID=''{0}  ;
select  top {2} * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_DataSource=1 and ATR_CheckType=0 and ATR_TypeID<>'' {0};
 select  top {3} * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_DataSource=1 and ATR_CheckType=1 and ATR_TypeID<>'' {0};
		  ", str, dta.Wpp, dta.Ppwjy, dta.Yjy));
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataSet ds = oBase.ExecuteDataSet(oCmd);
            if (ds.Tables.Count > 0)
            {
                return ds;
            }
            return new DataSet();
        }

        /// <summary>
        /// 获取重新分析数据案例
        /// </summary>
        /// <param name="dta"></param>
        /// <returns></returns>
        public DataSet dtReAnalyDataAL(Data dta)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append(" select  top 200 * from[Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where   CaseId = '99846831'  ");
            string str = "";
            if (!string.IsNullOrEmpty(dta.Ljd))
            {
                str += string.Format(" and ATR_ParagraphCode in({0}) ", dta.Ljd);
            }
            if (!string.IsNullOrEmpty(dta.Fy))
            {
                str += string.Format(" and ATR_CourtID in({0}) ", dta.Fy);
            }
            strSql.Append(string.Format(@"  select  top {1} * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where  ATR_DataSource=2 and ATR_TypeID=''{0}  ;
select  top {2} * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_DataSource=2 and ATR_CheckType=0 and ATR_TypeID<>'' {0};
 select  top {3} * from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_DataSource=2 and ATR_CheckType=1 and ATR_TypeID<>'' {0};
		  ", str, dta.Wpp, dta.Ppwjy, dta.Yjy));
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataSet ds = oBase.ExecuteDataSet(oCmd);
            if (ds.Tables.Count > 0)
            {
                return ds;
            }
            return new DataSet();
        }



        /// <summary>
        /// 插入存储争议焦点内容与争议焦点要素表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddSYS_ArgumentTextRelNew(SYS_ArgumentTextRel model)
        {
            try
            {
                string strSql = string.Format(" insert into [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel](ATR_ID,CaseId,ATR_Content,ATR_TypeID,ATR_CheckType,CauseId,ATR_DataSource,CaseCode,ATR_CaseType,ATR_BookType,ATR_ParagraphCode,ATR_CourtID)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}') ",
                        model.ATR_ID, model.CaseId, model.ATR_Content, model.ATR_TypeID, model.ATR_CheckType, model.CauseId, model.ATR_DataSource, model.CaseCode, model.ATR_CaseType, model.ATR_BookType, model.ATR_ParagraphCode, model.ATR_CourtID);
                Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }


        /// <summary>
        /// 先进行删除案例数据
        /// </summary>
        /// <param name="CaseId"></param>
        /// <param name="CauseId"></param>
        /// <param name="Atr_Content"></param>
        /// <returns></returns>
        public bool DeleteSys_ArgumentTextRel(string CaseId, string CauseId, string Atr_Content)
        {
            try
            {
                string strSql = string.Format("  delete from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_Content='{0}' and CauseId='{1}'and ATR_DataSource=2 and CaseId='{2}' ", Atr_Content, CauseId, CaseId);
                Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }



        /// <summary>
        /// 先进行删除实例数据
        /// </summary>
        /// <param name="CaseId"></param>
        /// <param name="CauseId"></param>
        /// <param name="Atr_Content"></param>
        /// <returns></returns>
        public bool DeleteSys_ArgumentTextRelSL(string CaseCode, string CauseId, string Atr_Content)
        {
            try
            {
                string strSql = string.Format("  delete from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_Content='{0}' and CauseId='{1}'and ATR_DataSource=1 and CaseCode='{2}' ", Atr_Content, CauseId, CaseCode);
                Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 删除数据根据atrID
        /// </summary>
        /// <param name="atrID"></param>
        /// <returns></returns>
        public bool DeleteArgumentTextRel(string atrID)
        {
            string strSql = string.Format(" delete from [Ldt_JudgeProcess].[dbo].[SYS_ArgumentTextRel] where ATR_ID='{0}' ", atrID);
            Database oBase = DatabaseFactory.CreateDatabase("JudgeProcess");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }
    }
}
